# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
import huayiQDB.controller.log as log



class HuayiqdbPipeline(object):
    logger = log.logger
    def __init__(self):
        self.logger.info("HuayiqdbPipeline.__init__.................")
        # self.conn = pymysql.connect(host="106.12.5.90", user="root", password="kkk@2286956", database="Qdb", charset="utf8",connect_timeout=2000)
        # self.cursor = self.conn.cursor()


    def process_item(self, item, spider):
        # self.logger.info('item_Info:------------------------')
        # self.logger.info('Item_Value:' + str(item['Item_Value']))
        # self.logger.info('Item_Name:' + str(item['Item_Name']))
        # self.logger.info('Course_Value:' + str(item['Course_Value']))
        # self.logger.info('Course_Name:' + str(item['Course_Name']))
        # self.logger.info('Study_Value:' + str(item['Study_Value']))
        # self.logger.info('Study_Name:' + str(item['Study_Name']))
        self.logger.info("HuayiqdbPipeline.process_item.................")
        # sql = "insert into tmp_huayi_db (Q_Name, Q_IDValue, Q_ResultValue, " \
        #       "A_Name, A_Value, Q_Course_Score, " \
        #       "Q_Study_Type,Q_Study_Name, Q_Course_Type, " \
        #       "Q_Course_Name, Q_Item_Type, Q_Item_Name, Q_Class_Type) values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (
        #     str(item['q_name']),
        #     str(item['q_IDValue']),
        #     str(item['result_value']),
        #     str(item['q_question_correct_name']),
        #     str(item['q_question_correct']),
        #     str(item['Course_score']),
        #     str(item['Study_Value']),
        #     str(item['Study_Name']),
        #     str(item['Course_Value']),
        #     str(item['Course_Name']),
        #     str(item['Item_Value']),
        #     str(item['Item_Name']),
        #     str(item['Class_Type'])
        # )

        sql = """insert into tmp_huayi_db (q_name, q_idvalue, q_resultvalue, a_name, a_value, q_course_score, q_study_type,q_study_name, q_course_type, q_course_name, q_item_type, q_item_name, q_class_type,a_name_all) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
        # self.logger.info("SQL.................:"+sql)
        db_sys = spider.db_sys
        conn = db_sys.get_conn()
        cursor = db_sys.get_cursor()
        try:
            # 执行sql语句
            cursor.execute(sql , (
                  str(item['q_name']),
                  str(item['q_IDValue']),
                  str(item['result_value']),
                  str(item['q_question_correct_name']),
                  str(item['q_question_correct']),
                  str(item['Course_score']),
                  str(item['Study_Value']),
                  str(item['Study_Name']),
                  str(item['Course_Value']),
                  str(item['Course_Name']),
                  str(item['Item_Value']),
                  str(item['Item_Name']),
                  str(item['Class_Type']),
                  item['a_name_all']
              ))
            conn.commit()
        except:
            # 如果发生错误则回滚
            conn.rollback()
            raise IOError("数据保存失败")
            conn.close()
            return item
        return item

    def open_spider(self,spider):
        self.logger.info("HuayiqdbPipeline.open_spider...................")
        pass

    def close_spider(self,spider):
        self.logger.info("HuayiqdbPipeline.close_spider..................")
        spider.db_sys.db_close()
        pass
